﻿using System.Data;
using System.Data.SqlClient;
using Pantry.Commons;
using Pantry.Entity;

namespace Pantry.DataAccess
{
    public class ServiceOrgRelateDao
    {
        private readonly SqlConnection _sqlConnection;
        public ServiceOrgRelateDao()
        {
            _sqlConnection = SqlConnectionTool.SqlGetConnection();
        }
        #region Constant

        private const string SqlSelect = @"SELECT [Id],[OrganizationsID]
                                      ,[ServiceID]
                                      ,[ServiceParentID],[IsActive]
                                  FROM [ServiceOrgRelate] WHERE [ServiceParentID] = @ServiceParentID AND [OrganizationsID] = @OrganizationsID";
        private const string SqlInsert = @"INSERT INTO [ServiceOrgRelate]
                                               ([OrganizationsID]
                                               ,[ServiceID]
                                               ,[ServiceParentID]
                                               , [IsActive])
                                         VALUES
                                               (@OrganizationsID, 
                                               @ServiceID, 
                                               @ServiceParentID
                                               ,@IsActive)";
        private const string SqlUpdate = @"UPDATE [ServiceOrgRelate]
                                                   SET  [IsActive] = @IsActive
                                                 WHERE [ServiceParentID] = @ServiceParentID AND [OrganizationsID] = @OrganizationsID AND [ServiceID] = @ServiceID ";
        private const string SqlDelete = @"DELETE FROM  [ServiceOrgRelate]  WHERE [ServiceParentID] = @ServiceParentID AND [OrganizationsID] = @OrganizationsID AND[ServiceID] = @ServiceID";
        private const string SqlCheckExist = @"SELECT Count([Id])
                                  FROM [ServiceOrgRelate] WHERE [ServiceParentId] = @ServiceParentId AND [OrganizationsID] = @OrganizationsID AND [ServiceID] = @ServiceID";
        #endregion

        /// <summary>
        /// Selects the specified service parent id.
        /// </summary>
        /// <param name="serviceParentId">The service parent id.</param>
        /// <param name="organizationsId">The Organizations id.</param>
        /// <returns></returns>
        public DataTable Select (int serviceParentId,int organizationsId )
        {
            var param = new SqlParameter[2];
             param[0] = new SqlParameter("@ServiceParentID", SqlDbType.Int) { Value = serviceParentId };
             param[1] = new SqlParameter("@OrganizationsID", SqlDbType.Int) { Value = organizationsId };
             return SqlHelper.ExecuteDataSet(_sqlConnection, CommandType.Text, SqlSelect, param).Tables[0];
        }
        /// <summary>
        /// Inserts the specified service org Relate.
        /// </summary>
        /// <param name="serviceOrgRelate">The service org Relate.</param>
        /// <returns></returns>
        public  bool Insert(ServiceOrgRelate serviceOrgRelate)
        {
            var param = new SqlParameter[4];
            param[0] = new SqlParameter("@ServiceParentID", SqlDbType.Int) { Value = serviceOrgRelate.ServiceParentId };
            param[1] = new SqlParameter("@OrganizationsID", SqlDbType.Int) { Value = serviceOrgRelate.OrganizationId };
            param[2] = new SqlParameter("@ServiceID", SqlDbType.Int) { Value = serviceOrgRelate.ServiceId };
            param[3] = new SqlParameter("@IsActive", SqlDbType.Int) { Value = serviceOrgRelate.IsActive };
            return SqlHelper.ExecuteNonQuery(_sqlConnection, CommandType.Text, SqlInsert, param) != 0;
        }
        /// <summary>
        /// Updates the specified service org Relate.
        /// </summary>
        /// <param name="serviceOrgRelate">The service org Relate.</param>
        /// <returns></returns>
        public  bool Update(ServiceOrgRelate serviceOrgRelate)
        {
            var param = new SqlParameter[4];
            param[0] = new SqlParameter("@ServiceParentID", SqlDbType.Int) { Value = serviceOrgRelate.ServiceParentId };
            param[1] = new SqlParameter("@OrganizationsID", SqlDbType.Int) { Value = serviceOrgRelate.OrganizationId };
            param[2] = new SqlParameter("@ServiceID", SqlDbType.Int) { Value = serviceOrgRelate.ServiceId };
            param[3] = new SqlParameter("@IsActive", SqlDbType.Int) { Value = serviceOrgRelate.IsActive };
            return SqlHelper.ExecuteNonQuery(_sqlConnection, CommandType.Text, SqlUpdate, param) != 0;
        }
        /// <summary>
        /// Deletes the specified service org Relate.
        /// </summary>
        /// <param name="serviceOrgRelate">The service org Relate.</param>
        /// <returns></returns>
        public  bool Delete(ServiceOrgRelate serviceOrgRelate)
        {
            var param = new SqlParameter[3];
            param[0] = new SqlParameter("@ServiceParentID", SqlDbType.Int) { Value = serviceOrgRelate.ServiceParentId };
            param[1] = new SqlParameter("@OrganizationsID", SqlDbType.Int) { Value = serviceOrgRelate.OrganizationId };
            param[2] = new SqlParameter("@ServiceID", SqlDbType.Int) { Value = serviceOrgRelate.ServiceId };
            return SqlHelper.ExecuteNonQuery(_sqlConnection, CommandType.Text, SqlDelete, param) != 0;
        }
        /// <summary>
        /// Determines whether the specified service org relate is exist.
        /// </summary>
        /// <param name="serviceOrgRelate">The service org relate.</param>
        /// <returns>
        ///   <c>true</c> if the specified service org relate is exist; otherwise, <c>false</c>.
        /// </returns>
        public bool IsExist(ServiceOrgRelate serviceOrgRelate)
        {
            var param = new SqlParameter[3];
            param[0] = new SqlParameter("@ServiceParentId", SqlDbType.Int) { Value = serviceOrgRelate.ServiceParentId };
            param[1] = new SqlParameter("@OrganizationsID", SqlDbType.Int) { Value = serviceOrgRelate.OrganizationId };
            param[2] = new SqlParameter("@ServiceID", SqlDbType.Int) { Value = serviceOrgRelate.ServiceId };
            return SqlHelper.ExecuteScalar(_sqlConnection, CommandType.Text, SqlCheckExist, param).ConvertToDataTable().Rows.Count > 0;
        }
    }
}
